﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessTwoCanteenSummaryReport
// 文件功能描述：二食堂汇总报表数据访问层
//
// 
// 创建标识：2012-03-22 王冲
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    public class DataAccessTwoCanteenSummaryReport
    {
        /// <summary>
        /// 获取部门列表（用于绑定下拉框）
        /// </summary>
        /// <returns></returns>
        public DataSet GetdeptName()
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select Dep_ID,deptName from FILE_DEPARTMENT order by Dep_ID";
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        /// <summary>
        /// 获取二食堂汇总表
        /// </summary>
        /// <returns></returns>
        public DataSet BindList(int pagesize, int startindex, object[] obj, string tablename, out int count)
        {
            /*
             * 先调用sp_CreateTabelForConZHDate存储过程创建一个表名为tablename的表,用来存储汇总数据
             */
            sqldb db = new sqldb();
            SqlParameter[] parameters = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30)
                                        };
            parameters[0].Value = tablename;
            db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "sp_CreateTabelForConZHDate", parameters);

            /*
             * 从Con_Period表中取出编号为1和4(早餐和夜宵)的信息，将早餐的开始时间与夜宵的结束时间与夜宵是否跨天取出,
             */
            string query = "select * from Con_Period where PeriodCode =1 or PeriodCode =4";
            DataSet dsPerCode = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            string strBT = dsPerCode.Tables[0].Rows[0]["BeginTime"].ToString();
            string strET = dsPerCode.Tables[0].Rows[1]["EndTime"].ToString();
            string strND = dsPerCode.Tables[0].Rows[1]["NextDay"].ToString();

            /*
             * 再从开始日期循环到结束日期，调用sp_ConRepHzForDate存储过程向刚才新建的表里面逐一的分析出数据添加到表中,每天一条。
             * 添加的时候判断一下如果夜宵跨天的话，就将结束时间的天数加1。
             */
            for (DateTime de = Convert.ToDateTime(obj[0]); de <= Convert.ToDateTime(obj[1]); de = de.AddDays(1))
            {
                string strtime = de.ToString("yyyy-MM-dd") + " " + strET;
                if (strND == "True")
                {
                    strtime = (Convert.ToDateTime(strtime).AddDays(1)).ToString();
                }
                SqlParameter[] parameters1 = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30),
                                            new SqlParameter("@TYPEID", SqlDbType.Int),
                                            new SqlParameter("@CURDATA", SqlDbType.DateTime),
                                            new SqlParameter("@INBEGDATETIME", SqlDbType.VarChar, 20),
                                            new SqlParameter("@INENDDATETIME", SqlDbType.VarChar, 20),
                                            new SqlParameter("@DEPTID", SqlDbType.VarChar, 500),
                                            new SqlParameter("@EmpNo", SqlDbType.VarChar, 20),
                                            new SqlParameter("@PERSONNELTYPEID", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NAME", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NodeID", SqlDbType.VarChar, 50),
                                            new SqlParameter("@MACNO", SqlDbType.VarChar, 100),
                                            new SqlParameter("@DEALMARK", SqlDbType.VarChar, 300)
                                        };
                parameters1[0].Value = tablename;
                parameters1[1].Value = 1;
                parameters1[2].Value = de;
                parameters1[3].Value = de.ToString("yyyy-MM-dd") + " " + strBT;
                parameters1[4].Value = strtime;
                parameters1[5].Value = obj[2];
                parameters1[6].Value = obj[3];
                parameters1[7].Value = "%";
                parameters1[8].Value = obj[4];
                parameters1[9].Value = 2;
                parameters1[10].Value = obj[5];
                parameters1[11].Value = obj[6];
                db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "sp_ConRepHzForDate", parameters1);
            }
            /*
             * 获取刚创建的表中的记录数并用out返回用于分页
             */
            query = "select count(1) from " + tablename;
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            count = Convert.ToInt32(rowcount);

            /*
             * 从刚才创建的表中取出汇总数据，然后分页，根据日期排序
             */
            int startrow = pagesize * (startindex - 1);
            int endrow = pagesize * startindex + 1;
            query = "select * from (select ROW_NUMBER() OVER(ORDER BY SDAY DESC) rownumber,* " +
                           "from " + tablename + " where 1=1 ) B "
                         + "where B.rownumber > " + startrow.ToString() + " and B.rownumber < " + endrow.ToString();
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }

        public DataSet GetListAll(object[] obj, string tablename)
        {
            /*
             * 先调用sp_CreateTabelForDeposith存储过程创建一个表名为tablename的表,用来存储汇总数据
             */
            sqldb db = new sqldb();
            SqlParameter[] parameters = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30)
                                        };
            parameters[0].Value = tablename;
            db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "sp_CreateTabelForConZHDate", parameters);

            /*
             * 从Con_Period表中取出编号为1和4(早餐和夜宵)的信息，将早餐的开始时间与夜宵的结束时间与夜宵是否跨天取出,
             */
            string query = "select * from Con_Period where PeriodCode =1 or PeriodCode =4";
            DataSet dsPerCode = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            string strBT = dsPerCode.Tables[0].Rows[0]["BeginTime"].ToString();
            string strET = dsPerCode.Tables[0].Rows[1]["EndTime"].ToString();
            string strND = dsPerCode.Tables[0].Rows[1]["NextDay"].ToString();

            /*
             * 再从开始日期循环到结束日期，调用sp_ConRepHzForDate存储过程向刚才新建的表里面逐一的分析出数据添加到表中,每天一条。
             * 添加的时候判断一下如果夜宵跨天的话，就将结束时间的天数加1。
             */
            for (DateTime de = Convert.ToDateTime(obj[0]); de <= Convert.ToDateTime(obj[1]); de = de.AddDays(1))
            {
                string strtime = de.ToString("yyyy-MM-dd") + " " + strET;
                if (strND == "True")
                {
                    strtime = (Convert.ToDateTime(strtime).AddDays(1)).ToString();
                }
                SqlParameter[] parameters1 = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30),
                                            new SqlParameter("@TYPEID", SqlDbType.Int),
                                            new SqlParameter("@CURDATA", SqlDbType.DateTime),
                                            new SqlParameter("@INBEGDATETIME", SqlDbType.VarChar, 20),
                                            new SqlParameter("@INENDDATETIME", SqlDbType.VarChar, 20),
                                            new SqlParameter("@DEPTID", SqlDbType.VarChar, 500),
                                            new SqlParameter("@EmpNo", SqlDbType.VarChar, 20),
                                            new SqlParameter("@PERSONNELTYPEID", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NAME", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NodeID", SqlDbType.VarChar, 50),
                                            new SqlParameter("@MACNO", SqlDbType.VarChar, 100),
                                            new SqlParameter("@DEALMARK", SqlDbType.VarChar, 300)
                                        };
                parameters1[0].Value = tablename;
                parameters1[1].Value = 1;
                parameters1[2].Value = de;
                parameters1[3].Value = de.ToString("yyyy-MM-dd") + " " + strBT;
                parameters1[4].Value = strtime;
                parameters1[5].Value = obj[2];
                parameters1[6].Value = obj[3];
                parameters1[7].Value = "%";
                parameters1[8].Value = obj[4];
                parameters1[9].Value = 2;
                parameters1[10].Value = obj[5];
                parameters1[11].Value = obj[6];
                db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "sp_ConRepHzForDate", parameters1);
            }

            /*
             * 从刚才创建的表中取出汇总数据，根据日期排序
             */
            query = "select * from " + tablename + " order by SDAY DESC";
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }
    }
}
